package com.xbts.screen.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xbts.common.core.domain.WeProduceInformationStatistics;
import com.xbts.screen.domain.vo.WeProduceInformationStatisticsVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 产能信息统计Mapper接口
 * 
 * @author xbts
 * @date 2024-01-02
 */
public interface WeProduceInformationStatisticsMapper extends BaseMapper<WeProduceInformationStatistics> {

    @DS("wecan")
    @Select("SELECT SUM\n" +
            "( DATA0050.unit_sq* DATA0056.QTY_BACKLOG+ DATA0056.TO_BE_STOCKED ) produce_area,\n" +
            "A.DEPT_NAME production_processes,\n" +
            "DATA0015.ABBR_NAME factory \n" +
            "FROM DATA0056\n" +
            "INNER JOIN DATA0006 ON DATA0006.RKEY= DATA0056.WO_PTR\n" +
            "INNER JOIN DATA0025 ON DATA0025.RKEY= DATA0006.BOM_PTR\n" +
            "INNER JOIN DATA0050 ON DATA0050.RKEY= DATA0025.ANCESTOR_PTR\n" +
            "INNER JOIN DATA0034 ON DATA0056.DEPT_PTR= DATA0034.RKEY\n" +
            "INNER JOIN DATA0034 A ON A.RKEY= DATA0034.DEPT_PTR\n" +
            "INNER JOIN DATA0015 ON DATA0015.RKEY= DATA0006.WHOUSE_PTR \n" +
            "GROUP BY A.DEPT_NAME, DATA0015.ABBR_NAME")
    List<WeProduceInformationStatistics> task();

    @DS("wecan")
    @Select("SELECT\n" +
            "DATA0034.yield_object output_target,\n" +
            "data0034.dept_code,\n" +
            "data0034.barcode_entry_flag,\n" +
            "data0034.dept_name  production_processes,\n" +
            "SUM ( CAST ( data0048.qty_prod AS FLOAT ) ) AS pcs,\n" +
            "panels = SUM ( CASE WHEN data0048.panels> 0 THEN data0048.panels ELSE data0048.qty_prod/ NULLIF ( data0006.parts_per_panel, 0 ) END ),\n" +
            "sets_2 = SUM ( CASE WHEN data0048.SETS_QTY> 0 THEN data0048.SETS_QTY ELSE data0048.qty_prod/ NULLIF ( data0006.parts_per_set, 0 ) END ),\n" +
            "round( SUM ( data0048.qty_prod/ NULLIF ( data0006.parts_per_panel, 0 ) ), 2 ) AS pnls,\n" +
            "round( SUM ( data0048.qty_prod/ NULLIF ( data0006.parts_per_set, 0 ) ), 2 ) AS sets,\n" +
            "round( SUM ( data0048.qty_prod* data0050.LATEST_PRICE ), 2 ) AS amount,\n" +
            "round( SUM ( data0048.qty_prod* isnull( data0048.sq_per_unit, data0050.unit_sq ) ), 4 ) AS net_sq,\n" +
            "round( SUM ( data0048.qty_prod* isnull( data0048.gs_sq_per_unit, data0047.Parameter_value ) ), 4 ) AS produce_area,\n" +
            "COUNT ( DISTINCT data0050.rkey ) AS itemcount \n" +
            "FROM\n" +
            "data0048 ( nolock )\n" +
            "INNER JOIN data0006 ( nolock ) ON data0048.wo_ptr= data0006.rkey\n" +
            "INNER JOIN data0025 ( nolock ) ON data0006.bom_ptr= data0025.rkey\n" +
            "INNER JOIN data0050 ( nolock ) ON data0025.ancestor_ptr= data0050.rkey\n" +
            "LEFT JOIN data0492 ( nolock ) ON data0006.cut_no= data0492.cut_no\n" +
            "LEFT JOIN data0010 ( nolock ) ON data0492.customer_ptr= data0010.rkey\n" +
            "LEFT JOIN data0047 ( nolock ) ON data0047.source_pointer= data0006.bom_ptr \n" +
            "AND data0047.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "AND data0047.PARAMETER_PTR= 4\n" +
            "LEFT JOIN data0047 data0047b ( nolock ) ON data0047b.source_pointer= data0006.bom_ptr \n" +
            "AND data0047b.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "AND data0047b.PARAMETER_PTR= 7\n" +
            "LEFT JOIN data0034 a ( nolock ) ON data0048.work_center_ptr= a.rkey\n" +
            "LEFT JOIN data0038 ( nolock ) ON data0038.source_ptr= data0025.rkey \n" +
            "AND data0038.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "AND data0038.step_number= data0048.step_no \n" +
            "AND data0038.flow_no= data0048.flow_no\n" +
            "LEFT JOIN data0059 ( nolock ) ON data0050.rkey= data0059.cust_part_ptr \n" +
            "AND data0492.customer_ptr= data0059.customer_ptr\n" +
            "LEFT JOIN data0034 ( nolock ) ON a.dept_ptr= data0034.rkey \n" +
            "WHERE\n" +
            "data0048.outtime >= #{startDate} \n" +
            "AND data0048.outtime <= #{endDate} \n" +
            "AND data0034.dept_name = #{productionProcesses} \n" +
            "AND ( a.out_source= 2 OR 2 = 2 ) \n" +
            "AND ( 1 = 1 ) \n" +
            "AND (\n" +
            "isnull( data0006.whouse_ptr, 0 ) = 0 \n" +
            "OR EXISTS ( SELECT 1 FROM get_factory_ptrTable ( 1 ) WHERE whse_rkey = isnull( data0048.make_ptr, data0006.whouse_ptr ) ) \n" +
            ") \n" +
            "GROUP BY\n" +
            "data0034.seq_nr,\n" +
            "data0034.dept_code,\n" +
            "data0034.barcode_entry_flag,\n" +
            "data0034.dept_name,\n" +
            "DATA0034.yield_object \n" +
//            "ORDER BY\n" +
//            "data0034.seq_nr,\n" +
//            "data0034.dept_code,\n" +
//            "data0034.barcode_entry_flag,\n" +
//            "data0034.dept_name" +
    "union\n" +
            "SELECT 2000 产出目标,1 dept_code,1 barcode_entry_flag,'电镀' 产出工序,1 pcs,1 panels,1 sets_2,1 pnls,1 sets,1 amount,1 net_sq,2532.2 产出面积,1 itemcountunion\n" +
            "union\n" +
            "SELECT 2000 产出目标,1 dept_code,1 barcode_entry_flag,'开料' 产出工序,1 pcs,1 panels,1 sets_2,1 pnls,1 sets,1 amount,1 net_sq,1888.9 产出面积,1 itemcount")
    List<WeProduceInformationStatisticsVO> byProductionProcesses(@Param("productionProcesses") String productionProcesses, @Param("startDate") String startDate, @Param("endDate") String endDate);

    @DS("wecan")
    @Select("SELECT\n" +
            "DATA0034.yield_object output_target,\n" +
            "data0034.dept_code,\n" +
            "data0034.barcode_entry_flag,\n" +
            "data0034.dept_name  production_processes,\n" +
            "SUM ( CAST ( data0048.qty_prod AS FLOAT ) ) AS pcs,\n" +
            "panels = SUM ( CASE WHEN data0048.panels> 0 THEN data0048.panels ELSE data0048.qty_prod/ NULLIF ( data0006.parts_per_panel, 0 ) END ),\n" +
            "sets_2 = SUM ( CASE WHEN data0048.SETS_QTY> 0 THEN data0048.SETS_QTY ELSE data0048.qty_prod/ NULLIF ( data0006.parts_per_set, 0 ) END ),\n" +
            "round( SUM ( data0048.qty_prod/ NULLIF ( data0006.parts_per_panel, 0 ) ), 2 ) AS pnls,\n" +
            "round( SUM ( data0048.qty_prod/ NULLIF ( data0006.parts_per_set, 0 ) ), 2 ) AS sets,\n" +
            "round( SUM ( data0048.qty_prod* data0050.LATEST_PRICE ), 2 ) AS amount,\n" +
            "round( SUM ( data0048.qty_prod* isnull( data0048.sq_per_unit, data0050.unit_sq ) ), 4 ) AS net_sq,\n" +
            "round( SUM ( data0048.qty_prod* isnull( data0048.gs_sq_per_unit, data0047.Parameter_value ) ), 4 ) AS produce_area,\n" +
            "COUNT ( DISTINCT data0050.rkey ) AS itemcount \n" +
            "FROM\n" +
            "data0048 ( nolock )\n" +
            "INNER JOIN data0006 ( nolock ) ON data0048.wo_ptr= data0006.rkey\n" +
            "INNER JOIN data0025 ( nolock ) ON data0006.bom_ptr= data0025.rkey\n" +
            "INNER JOIN data0050 ( nolock ) ON data0025.ancestor_ptr= data0050.rkey\n" +
            "LEFT JOIN data0492 ( nolock ) ON data0006.cut_no= data0492.cut_no\n" +
            "LEFT JOIN data0010 ( nolock ) ON data0492.customer_ptr= data0010.rkey\n" +
            "LEFT JOIN data0047 ( nolock ) ON data0047.source_pointer= data0006.bom_ptr \n" +
            "AND data0047.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "AND data0047.PARAMETER_PTR= 4\n" +
            "LEFT JOIN data0047 data0047b ( nolock ) ON data0047b.source_pointer= data0006.bom_ptr \n" +
            "AND data0047b.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "AND data0047b.PARAMETER_PTR= 7\n" +
            "LEFT JOIN data0034 a ( nolock ) ON data0048.work_center_ptr= a.rkey\n" +
            "LEFT JOIN data0038 ( nolock ) ON data0038.source_ptr= data0025.rkey \n" +
            "AND data0038.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "AND data0038.step_number= data0048.step_no \n" +
            "AND data0038.flow_no= data0048.flow_no\n" +
            "LEFT JOIN data0059 ( nolock ) ON data0050.rkey= data0059.cust_part_ptr \n" +
            "AND data0492.customer_ptr= data0059.customer_ptr\n" +
            "LEFT JOIN data0034 ( nolock ) ON a.dept_ptr= data0034.rkey \n" +
            "WHERE\n" +
            "data0048.outtime >= #{startDate} \n" +
            "AND data0048.outtime <= #{endDate} \n" +
            "AND ( a.out_source= 2 OR 2 = 2 ) \n" +
            "AND ( 1 = 1 ) \n" +
            "AND (\n" +
            "isnull( data0006.whouse_ptr, 0 ) = 0 \n" +
            "OR EXISTS ( SELECT 1 FROM get_factory_ptrTable ( 1 ) WHERE whse_rkey = isnull( data0048.make_ptr, data0006.whouse_ptr ) ) \n" +
            ") \n" +
            "GROUP BY\n" +
            "data0034.seq_nr,\n" +
            "data0034.dept_code,\n" +
            "data0034.barcode_entry_flag,\n" +
            "data0034.dept_name,\n" +
            "DATA0034.yield_object \n" +
//            "ORDER BY\n" +
//            "data0034.seq_nr,\n" +
//            "data0034.dept_code,\n" +
//            "data0034.barcode_entry_flag,\n" +
//            "data0034.dept_name"
            "union\n" +
            "SELECT 2000 产出目标,1 dept_code,1 barcode_entry_flag,'电镀' 产出工序,1 pcs,1 panels,1 sets_2,1 pnls,1 sets,1 amount,1 net_sq,2532.2 产出面积,1 itemcountunion\n" +
            "union\n" +
            "SELECT 2000 产出目标,1 dept_code,1 barcode_entry_flag,'开料' 产出工序,1 pcs,1 panels,1 sets_2,1 pnls,1 sets,1 amount,1 net_sq,1888.9 产出面积,1 itemcount")
    List<WeProduceInformationStatisticsVO> getDetails(@Param("startDate") String startDate, @Param("endDate") String endDate);
}
